package ch.zbw.pra.sysventory.database.jdbcTemplate;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.mysql.jdbc.Statement;

import ch.zbw.pra.sysventory.database.mapper.NicMapper;
import ch.zbw.pra.sysventory.model.Nic;

/**
 * Greift auf die Datenbank zu (DB-Tabelle Computer) und delegiert das Mapping der Tabellenspalten an den RowMapper
 * Klasse kann auf die Datenbank schreiben, lesen und loeschen
 * 
 * @author Roberto Lucas
 */

public class NicJDBCTemplate implements Serializable  {
	
	private static final long serialVersionUID = 8666974976480319075L;
	private static Logger LOG = LoggerFactory.getLogger(NicJDBCTemplate.class);
	private JdbcTemplate jdbcTemplateObject;
	
	private static final String INSERT_SQL = "INSERT INTO Nic (nicName, nicIP4, nicIP6, nicMACAddress, " +
			"nicStandardGW, nicDHCP, nicSubnet, nic_compID_fk) VALUES(?,?,?,?,?,?,?,?)";

	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplateObject = new JdbcTemplate(dataSource);
	}
	
	public Integer insertNicJDBCTemplate(final Nic nic, final Integer computerId) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{
		jdbcTemplateObject.update(new PreparedStatementCreator() { 
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { 
				PreparedStatement ps = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS); 
					ps.setString(1, nic.getNameNic()); 
					ps.setString(2, nic.getIpv4Nic()); 
					ps.setString(3, nic.getIpv6Nic());
					ps.setString(4, nic.getMacAdressNic());
					ps.setString(5, nic.getStandardGatewayNic());
					ps.setString(6, nic.getDhcpNic());
					ps.setString(7, nic.getSubnetNic());
					ps.setInt(8, computerId);
					return ps; 
				} 
			}, keyHolder);
		} catch (DataAccessException e){
			LOG.debug("Failed to insert Nic for ComputerId "+computerId);
			LOG.error(e.toString());
		}
		LOG.debug("Inserted Nic with id: " + keyHolder.getKey());
		return keyHolder.getKey().intValue();
	}


	public List<Nic> listNics() {
		String SQL = "SELECT * FROM Nic GROUP BY nicName,nicIP4,nicIP6,nicMACAddress," +
				"nicStandardGW,nicDHCP,nicSubnet";
		List<Nic> nics = new ArrayList<Nic>();
		try{
			nics = jdbcTemplateObject.query(SQL,
					new NicMapper());
		} catch(DataAccessException e){
			LOG.debug("Failed to load a list of Nics");
			LOG.error(e.toString());
		}
		LOG.debug("Loaded a list of Nics");
		return nics;
	}
	
	public Nic readNicById(Integer nicId){
		String SQL = "SELECT * FROM Nic WHERE nicID_pk = ?";
		Nic nic = new Nic();
		try{
			nic = jdbcTemplateObject.queryForObject(SQL, 
	                        new Object[]{nicId,}, new NicMapper());
	      LOG.debug("Read Nic with Id = "+ nic.getNicId());
		} catch(DataAccessException e){
			LOG.debug("Failed to read Nic with id "+nicId);
			LOG.error(e.toString());
		}
		LOG.debug("Read Nic wiht Id= "+nic.getNicId());
		return nic;
	}
	
	public void deleteNicById(Integer nicId) {
		String SQL = "DELETE FROM Nic WHERE nicID_pk = ?";
		try{
			jdbcTemplateObject.update(SQL, nicId);			
		} catch(DataAccessException e){
			LOG.debug("Failed to delete Nic with id "+nicId);
			LOG.error(e.toString());
		}
		LOG.debug("Deleted NIc with id = " + nicId);
	}

	public List<Nic> listAllNicsFromComputer(Integer computerId) {
		String SQL = "SELECT * FROM Nic where nic_compID_fk = ?";
		List<Nic> nics = new ArrayList<Nic>();
		try{
			nics = jdbcTemplateObject.query(SQL, new Object[]{computerId},
				new NicMapper());
		} catch(DataAccessException e){
			LOG.debug("Failed to load a List of Nics for ComputerID "+computerId);
			LOG.error(e.toString());
		}
		LOG.debug("Loaded a a list of Nics for ComputerId "+computerId);
		return nics;
	}
}
